package com.xhui.vimarket.zkplus.utils.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableFont;

import org.zkoss.zul.Columns;
import org.zkoss.zul.Detail;
import org.zkoss.zul.Filedownload;
import org.zkoss.zul.Grid;
import org.zkoss.zul.Label;
import org.zkoss.zul.Listbox;
import org.zkoss.zul.Listcell;
import org.zkoss.zul.Listhead;
import org.zkoss.zul.Listheader;
import org.zkoss.zul.Listitem;
import org.zkoss.zul.Messagebox;
import org.zkoss.zul.Rows;

/**
 * Excel.
 * 
 * @版权：福富软件 版权所有 (c) 2011
 * @author wuyx
 * @version Revision 1.0.0
 * @see:
 * @创建日期：2011-2-11
 * @功能说明：
 * 
 */
public final class Excel {
    
    /**
     * FONT_SIZE.
     */
    private static final int FONT_SIZE = 11;
    
    /**
     * 私有构造函数.
     * 
     * @author wuyx
     * 2011-2-11 wuyx 
     */
    private Excel() {
    }
    
    /**
     * write.
     * 
     * @param sheetList List<Sheet>
     * @param os OutputStream
     * @author wuyx
     * 2011-2-11 wuyx 
     * @throws IOException Exception
     */
    public static void write(List<Sheet> sheetList, OutputStream os) throws IOException {
        jxl.write.WritableWorkbook wwb;
        jxl.write.WritableFont wf1 = new jxl.write.WritableFont(WritableFont.ARIAL, FONT_SIZE,
            WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        jxl.write.WritableCellFormat wff1 = new jxl.write.WritableCellFormat(NumberFormats.TEXT);
        try {
            // wff1.setBackground(jxl.format.Colour.VIOLET2);
            // wff1.setBackground(jxl.format.Colour.VIOLET2);
            wwb = Workbook.createWorkbook(os);
            int i = 0;
            for (Iterator<Sheet> itr = sheetList.iterator(); itr.hasNext();) {
                Sheet sheet = itr.next();
                int rowNum = 0;
                jxl.write.WritableSheet ws = wwb.createSheet(sheet.getName(), i);
                for (Iterator<Row> sheetItr = sheet.getRowList().iterator(); sheetItr.hasNext();) {
                    Row row = sheetItr.next();
                    int colunNum = 0;
                    if (row.getHeight() > -1) { // 高度设置
                        ws.setRowView(rowNum, row.getHeight());
                    }
                    for (Iterator<Column> rowIterator = row.getColumnList().iterator(); rowIterator
                        .hasNext();) {
                        Column column = rowIterator.next();
                        jxl.write.Label label = new jxl.write.Label(colunNum, rowNum, column
                            .getColumnLabel(), wff1);
                        if (column.getDataValidationRange() != null) { // data range
                            WritableCellFeatures wcfc = new WritableCellFeatures();
                            wcfc.setDataValidationList(column.getDataValidationRange());
                            ((jxl.write.WritableCell) label).setCellFeatures(wcfc);
                        }
                        ws.addCell(label);
                        colunNum++;
                    }
                    rowNum++;
                }
                i++;
            }
            // 写入Exel工作表
            wwb.write();
            // 关闭Excel工作薄对象
            wwb.close();
            // 关闭流
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            os.close();
        }
    }
    
    /**
     * 导出Listbox到Excel.
     * @author OUZHF
     * @param listbox
     * @throws Exception
     */
    public static void listboxToExcel(Listbox listbox) throws Exception {
        if (listbox == null || listbox.getItems().size() == 0) {
            Messagebox.show("数据为空！", "提示信息", Messagebox.OK, Messagebox.INFORMATION);
        } else {
            Date dat = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            String timeStr = sdf.format(dat);
            InputStream is = getInputStream(listbox);
            if (is != null) {
                Filedownload.save(is, "application/vnd.ms-excel", timeStr + ".xls");
            } else {
                Messagebox.show("导出Excel失败！", "失败信息", Messagebox.OK, Messagebox.ERROR);
            }
            is.close();
        }
    }
    
    /**
     * .
     * @author OUZHF
     * @param listbox
     * @return
     * @throws Exception
     */
    public static InputStream getInputStream(Listbox listbox) throws Exception {
        Sheet sheet = new Sheet();
        sheet.setName("Sheet");
        appendTitleRow(sheet, listbox.getListhead());
        List list = listbox.getItems();
        for (Iterator iter = list.iterator(); iter.hasNext();) {
            Row row = new Row();
            Listitem listitem = (Listitem) iter.next();
            List columnList = new ArrayList();
            for (int i = 0; i < listitem.getChildren().size(); i++) {
                Column column = new Column();
                column.setColumnLabel(((Listcell) listitem.getChildren().get(i)).getLabel());
                column.setColumnNum(i);
                columnList.add(column);
            }
            row.setColumnList(columnList);
            sheet.appendRow(row);
        }
        List<Sheet> sheetList = new ArrayList();
        sheetList.add(sheet);
        
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        Excel.write(sheetList, stream);
        InputStream is = new ByteArrayInputStream(stream.toByteArray());
        stream.close();
        return is;
    }
    
    /**
     * .
     * @author OUZHF
     * @param sheet
     * @return
     */
    private static Row appendTitleRow(Sheet sheet, Listhead listhead) {
        Row row = new Row();
        List columnList = new ArrayList();
        for (int i = 0; i < listhead.getChildren().size(); i++) {
            Listheader listheader = (Listheader) listhead.getChildren().get(i);
            Column column = new Column();
            column.setColumnLabel(listheader.getLabel());
            column.setColumnNum(i);
            row.appendColumn(column);
        }
        sheet.appendRow(row);
        return row;
    }
    
    /**
     * sheet 表头拼接.
     * @param sheet sheet
     * @param columns 字段
     * @author g.luxb
     * @创建日期 2011-7-24
     */
    private static void appendTitleRow(Sheet sheet, Columns columns) {
        Row row = new Row();
        for (int i = 0; i < columns.getChildren().size(); i++) {
            org.zkoss.zul.Column c = (org.zkoss.zul.Column) columns.getChildren().get(i);
            if (c.getLabel() != null && !"".equals(c.getLabel())) {
                Column column = new Column();
                column.setColumnLabel(c.getLabel());
                column.setColumnNum(i);
                row.appendColumn(column);
            }
        }
        sheet.appendRow(row);
    }
    
    /**
     * sheet 表内容拼接.
     * @param sheet sheet
     * @param rows 字段
     * @author g.luxb
     * @创建日期 2011-7-24
     */
    private static void appendContentRow(Sheet sheet, Rows rows) {
        for (int i = 0; i < rows.getChildren().size(); i++) {
            Row row = new Row();
            org.zkoss.zul.Row r = (org.zkoss.zul.Row) rows.getChildren().get(i);
            List columnList = new ArrayList();
            for (int j = 0; j < r.getChildren().size(); j++) {
                Object obj = r.getChildren().get(j);
                if (obj instanceof Detail) {
                    continue;
                }
                Column column = new Column();
                column.setColumnLabel(((Label) obj).getValue());
                column.setColumnNum(j);
                columnList.add(column);
            }
            row.setColumnList(columnList);
            sheet.appendRow(row);
        }
    }
    
    /**
     * 导出Grid到Excel.
     * @author g.luxb
     * @param grid grid
     * @throws Exception 异常
     * @author g.luxb
     * @创建日期 2011-7-24
     */
    public static void gridToExcel(Grid grid) throws Exception {
        //g.getColumns().getChildren().get(1);
        //Row r = (Row) g.getRows().getChildren().get(1);
        //r.getChildren().get(1);
        if (grid == null || grid.getRows() == null || grid.getRows().getChildren().size() == 0) {
            Messagebox.show("数据为空！", "提示信息", Messagebox.OK, Messagebox.INFORMATION);
        } else {
            Sheet sheet = new Sheet();
            sheet.setName("Sheet");
            appendTitleRow(sheet, grid.getColumns());
            appendContentRow(sheet, grid.getRows());
            List<Sheet> sheetList = new ArrayList();
            sheetList.add(sheet);
            
            Date dat = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            String timeStr = sdf.format(dat);
            
            ByteArrayOutputStream stream = new ByteArrayOutputStream();
            Excel.write(sheetList, stream);
            InputStream is = new ByteArrayInputStream(stream.toByteArray());
            if (is != null) {
                Filedownload.save(is, "application/vnd.ms-excel", timeStr + ".xls");
            } else {
                Messagebox.show("导出Excel失败！", "失败信息", Messagebox.OK, Messagebox.ERROR);
            }
            stream.close();
        }
    }
}
